package com.jy.datapipeline.export.dao.impl;

import com.jy.datapipeline.export.dao.ExpTaskDao;
import com.jy.datapipeline.export.entity.ExpTaskModel;
import com.jy.datapipeline.common.util.AssertUtil;
import com.jy.datapipeline.common.util.DateUtils;
import com.jy.datapipeline.common.util.JdbcUtil;
import com.jy.datapipeline.common.constants.ExpConstants;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

@Slf4j
@Repository
public class ExpTaskDaoImpl implements ExpTaskDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<Map<String, Object>> findExpTaskByList(String taskName, String taskType,
                                                       int iDisplayStart, int iDisplayLength, String storeId, String ruleId, String status, String dataSourceId, String latestStatus) {
        List<Map<String, Object>> list=new ArrayList<>();
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<>();
            sb.append("SELECT a.id,a.task_id,a.rule_id,a.data_source_id,a.task_name,"
                + " a.status,a.store_id,a.task_type,a.crontab"
                + " ,DATE_FORMAT(a.update_time,'%Y-%m-%d %H:%i:%s') as update_time,"
                + " DATE_FORMAT(a.netexec_time,'%Y-%m-%d %H:%i:%s') as netexec_time,"
                + " b.rule_name,c.store_name,d.data_source_name,a.business_name,a.message,"
                + " city_code_id as cityCodeId, a.business_name"
                + "  from exp_task a  left join exp_rule b on a.rule_id=b.rule_id "
                + " left join data_store_info c on a.store_id=c.store_id "
                + " left join data_source_info d on a.data_source_id=d.data_source_id"
                + " where 1=1  ");
            if(taskName!=null && !taskName.equals("")){
                sb.append(" and a.task_name like :taskName");
                params.put("taskName","%"+taskName+"%");
            }
            if(taskType!=null && !taskType.equals("")){
                sb.append(" and a.task_type = :taskType");
                params.put("taskType",taskType);
            }
            if(storeId!=null && !storeId.equals("")){
                sb.append(" and a.store_id = :storeId");
                params.put("storeId",storeId);
            }
            if(ruleId!=null && !ruleId.equals("")){
                sb.append(" and a.rule_id = :ruleId");
                params.put("ruleId",ruleId);
            }
            if(status!=null && !status.equals("")){
                sb.append(" and a.status = :status");
                params.put("status",Integer.parseInt(status));
            }
            if(dataSourceId!=null && !dataSourceId.equals("")){
                sb.append(" and a.data_source_id = :dataSourceId");
                params.put("dataSourceId",dataSourceId);
            }
            if (latestStatus != null && !latestStatus.equals("") && Integer.parseInt(latestStatus) != 0) {
                sb.append(" and a.task_id = (select e.task_id from exp_task_execute_detail_log e where e.task_id = a.task_id and e.exp_status = :latestStatus and e.update_time =" +
                        " (select MAX(update_time) from exp_task_execute_detail_log where task_id = a.task_id))");
                params.put("latestStatus", latestStatus);
            } else if (latestStatus != null && !latestStatus.equals("")) {
                sb.append(" and a.status = :latestStatus");
                sb.append(" and not exists(select 1 from exp_task_execute_detail_log e where e.task_id = a.task_id)");
                params.put("latestStatus", latestStatus);
            }
            sb.append(" order by a.update_time desc  ");
            if(iDisplayStart>-1 && iDisplayLength>-1){
                sb.append("LIMIT  :iDisplayStart ,:iDisplayLength ;");
                params.put("iDisplayStart",iDisplayStart);
                params.put("iDisplayLength",iDisplayLength);
            }
            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
        }catch (Exception e){
            log.error("findExpTaskByList exception",e);
        }

        return list;
    }

    @Override
    public int findExpTaskByCount(String taskName, String taskType, String storeId, String ruleId,
                                  String status, String dataSourceId, String latestStatus) {
        int count=0;
        List<Map<String, Object>> list=new ArrayList<>();
        try {
            StringBuffer sb=new StringBuffer();
            Map<String,Object> params = new HashMap<String,Object>();
            sb.append("SELECT COUNT(a.id) as count from exp_task a   "
                + " where 1=1  ");
            if(taskName!=null && !taskName.equals("")){
                sb.append(" and a.task_name like :taskName");
                params.put("taskName","%"+taskName+"%");
            }
            if(storeId!=null && !storeId.equals("")){
                sb.append(" and a.store_id = :storeId");
                params.put("storeId",storeId);
            }
            if(status!=null && !status.equals("")){
                sb.append(" and a.status = :status");
                params.put("status",Integer.parseInt(status));
            }
            if(dataSourceId!=null && !dataSourceId.equals("")){
                sb.append(" and a.data_source_id = :dataSourceId");
                params.put("dataSourceId",dataSourceId);
            }
            if(ruleId!=null && !ruleId.equals("")){
                sb.append(" and a.rule_id = :ruleId");
                params.put("ruleId",ruleId);
            }
            if(taskType!=null && !taskType.equals("")){
                sb.append(" and a.task_type = :taskType");
                params.put("taskType",taskType);
            }
            if (latestStatus != null && !latestStatus.equals("") && Integer.parseInt(latestStatus) != 0) {
                sb.append(" and a.task_id = (select e.task_id from exp_task_execute_detail_log e where e.task_id = a.task_id and e.exp_status = :latestStatus and e.update_time =" +
                        " (select MAX(update_time) from exp_task_execute_detail_log where task_id = a.task_id))");
                params.put("latestStatus", latestStatus);
            } else if (latestStatus != null && !latestStatus.equals("")) {
                sb.append(" and a.status = :latestStatus");
                params.put("latestStatus",Integer.parseInt(latestStatus));
            }
            sb.append(" order by a.update_time desc  ");

            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
            if(list.size()>0){
                count=Integer.parseInt(list.get(0).get("count")!=null?list.get(0).get("count").toString():"0");
            }
        }catch (Exception e){
            log.error("findExpTaskByCount exception",e);
        }

        return count;
    }

    @Override
    public boolean updateExpTask(ExpTaskModel etm) {
        Map<String,Object> params=new HashMap<>();
        String sql="update exp_task set rule_id=:rule_id,data_source_id=:data_source_id,"
            + " task_name=:task_name,status=:status,store_id=:store_id,task_type=:task_type,update_time=NOW(),business_name=:business_name,city_code_id = :cityCodeId "
            + "   ";
        params.put("rule_id",etm.getRuleId());
        params.put("data_source_id",etm.getDataSourceId());
        params.put("task_name",etm.getTaskName());
        params.put("status",etm.getStatus());
        params.put("store_id",etm.getStoreId());
        params.put("task_type",etm.getTaskType());
        params.put("business_name",etm.getBusinessName());
        params.put("cityCodeId", etm.getCityCodeId());
        if(etm.getTaskType()==2){
            sql+=" ,crontab=:crontab,netexec_time=:netexec_time ";
            params.put("crontab",etm.getCrontab());
            params.put("netexec_time",etm.getNextexec_time()!=null?
                DateUtils.formatDate(etm.getNextexec_time(),"YYYY-MM-dd HH:mm:ss"):
                "");
        }
        if(etm.getMessage()!=null && !etm.getMessage().equals("")){
             sql+=",message=:message";
             params.put("message",etm.getMessage());
        }
        sql+=" where task_id=:task_id";
        params.put("task_id",etm.getTaskId());
        int req=this.namedParameterJdbcTemplate.update(sql,params);
        return req>0?true:false;
    }

    @Override
    public boolean deleteExpTask(String taskId) {
        Map<String,Object> parasm=new HashMap<>();
        String sql="delete from exp_task where task_id=:taskId ";
        parasm.put("taskId",taskId);
        int req=this.namedParameterJdbcTemplate.update(sql,parasm);
        return req>0?true:false;
    }

    @Override
    public boolean  updateExpTaskStatus(int status, String taskId,String netExecuTime) {
        Map<String,Object> parasm=new HashMap<>();
        String sql="update  exp_task set status=:status ,update_time=NOW(),message='' ";
        if(netExecuTime!=null && !netExecuTime.equals("")){
            sql+=" ,netexec_time=:netexec_time ";
            parasm.put("netexec_time",netExecuTime);
        }
        sql+="where task_id=:taskId ";
        parasm.put("status",status);
        parasm.put("taskId",taskId);
    int req=this.namedParameterJdbcTemplate.update(sql,parasm);
//        String sql="update  exp_task set status="+status+",update_time=NOW() ";
//        if(netExecuTime!=null && !netExecuTime.equals("")){
//            sql+=" ,netexec_time='"+netExecuTime+"' ";
//            //parasm.put("netexec_time",netExecuTime);
//        }
//        sql+="where task_id='"+taskId+"' ";
//        parasm.put("status",status);
//        parasm.put("taskId",taskId);
//        log.info(" update status->"+sql);
//       int req=this.jdbcTemplate.update(sql);
        return req>0?true:false;
    }

    @Override
    public List<Map<String, Object>> findExecuTask() {
        List<Map<String, Object>> list=new ArrayList<>();
        try{
            Map<String,Object> params=new HashMap<>();
            StringBuilder sb=new StringBuilder();
            long time=new Date().getTime();
            String nowTime=DateUtils.formatDate(new Date(time),"YYYY-MM-dd HH:mm:ss");
            sb.append("select * from exp_task where task_type=2 and status=1 and netexec_time<=:nowTime for update ");
            params.put("nowTime",nowTime);
            list=this.namedParameterJdbcTemplate.queryForList(sb.toString(),params);
        }catch (Exception e){
            log.error("findExecuTask error",e);
        }
        return list;
    }

    @SneakyThrows
    @Override
    public Set<Integer> batchSaveExpTask(List<ExpTaskModel> expTaskModelList) {
        String sql = "insert into exp_task(task_id, task_type, task_name, crontab, business_name, data_source_id, rule_id, netexec_time, status, store_id, city_code_id)" +
                "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        final JdbcUtil.JdbcConnectionPool jdbcConnectionPool = JdbcUtil.JdbcConnectionPool.getInstance();
        final Set<Integer> ids = new HashSet<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcConnectionPool.getConnection();
            preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            //执行批量添加
            connection.setAutoCommit(false);
            this.executeBatchSave(expTaskModelList, preparedStatement);
            connection.commit();
            //获取主键集
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            while (generatedKeys.next()) {
                ids.add(generatedKeys.getInt(1));
            }
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        }  finally {
            jdbcConnectionPool.release(connection, preparedStatement);
        }
        return ids;
    }

    private void executeBatchSave(List<ExpTaskModel> expTaskModelList, PreparedStatement preparedStatement) throws SQLException {
        for (ExpTaskModel expTaskModel : expTaskModelList) {
            preparedStatement.setString(1, expTaskModel.getTaskId());
            preparedStatement.setInt(2, expTaskModel.getTaskType());
            preparedStatement.setString(3, expTaskModel.getTaskName());
            preparedStatement.setString(4, expTaskModel.getCrontab() != null? expTaskModel.getCrontab() : null);
            preparedStatement.setString(5, expTaskModel.getBusinessName() != null? expTaskModel.getBusinessName() : null);
            preparedStatement.setString(6, expTaskModel.getDataSourceId());
            preparedStatement.setString(7, expTaskModel.getRuleId());
            Timestamp timestamp = null;
            if (expTaskModel.getNextexec_time() != null) {
                timestamp = new Timestamp(expTaskModel.getNextexec_time().getTime());
            }
            preparedStatement.setTimestamp(8, timestamp);
            preparedStatement.setInt(9, expTaskModel.getStatus());
            preparedStatement.setString(10, expTaskModel.getStoreId());
            preparedStatement.setInt(11, expTaskModel.getCityCodeId());
            preparedStatement.addBatch();
        }
        int[] ints = preparedStatement.executeBatch();
        AssertUtil.totalAffectedRows(ints.length, expTaskModelList, ExpConstants.BATCH_ADD_FAIL);
    }

    @Override
    public void batchDeleteExpTask(Set<Long> ids) {
        List<Map<String, Object>> params = new ArrayList<>();
        Map<String, Object> param;
        for (Long id : ids) {
            param = new HashMap<>();
            param.put("id", id);
            params.add(param);
        }
        String sql = "delete from exp_task where id = :id";
        int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(params));
        AssertUtil.totalAffectedRows(ints.length, ids, ExpConstants.BATCH_DELETE_FAIL);
    }

    @Override
    public Set<String> findTaskIds(Set<Long> ids) {
        Set<String> idSet = new HashSet<>();

        //拼接sql
        StringBuilder sql = new StringBuilder();
        sql.append("select task_id from exp_task where id in (");
        for (int i = 0; i < ids.size(); i++) {
            sql.append("?");
            if (ids.size() > i + 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        final JdbcUtil.JdbcConnectionPool jdbcConnectionPool = JdbcUtil.JdbcConnectionPool.getInstance();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcConnectionPool.getConnection();
            preparedStatement = connection.prepareStatement(sql.toString());

            //设置参数
            Iterator<Long> iterator = ids.iterator();
            int index = 1;
            while (iterator.hasNext()) {
                preparedStatement.setLong(index++, iterator.next());
            }
            ResultSet resultSet = preparedStatement.executeQuery();

            //添加结果
            while (resultSet.next()) {
                idSet.add(resultSet.getString("task_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcConnectionPool.release(connection, preparedStatement);
        }
        return idSet;
    }
}
